package com.dnjn.mes.biz.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.dnjn.mes.biz.domain.MesRecordExcept;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import java.util.List;

/**
 * <p>
 * 异常报警表，正确状态不写入 Mapper 接口
 * </p>
 *
 * @author Amber
 * @since 2023-07-26
 */
public interface MesRecordExceptMapper extends BaseMapper<MesRecordExcept> {

    @Select("SELECT count(*) FROM mes_record_except where TO_DAYS(create_time) = TO_DAYS(NOW()) and equipment_id=#{equipmentId} ")
    Integer countRecords(@Param("equipmentId") long equipmentId);

    @Update("UPDATE mes_record_except SET except_status=#{exceptStatus},resolved_time=#{resolvedTime},update_by=#{updateBy},duration_hour=TRUNCATE(TIMESTAMPDIFF(MINUTE,start_datetime,#{resolvedTime})/60,2) WHERE record_except_id= #{recordExceptId}")
    int updateRecordStatus(MesRecordExcept domain);

    List<MesRecordExcept> getRecordExceptList(@Param("equipmentId") long equipmentId);

    @Select(" SELECT COUNT(*) AS counts,DATE_FORMAT(create_time,'%Y-%m-%d') AS currentDate FROM mes_record_except WHERE equipment_id=#{equipmentId} AND create_time BETWEEN #{startTime} AND #{endTime} GROUP BY DATE_FORMAT(create_time,'%Y-%m-%d') ORDER BY create_time ")
    List<MesRecordExcept> countRecordExcepts(@Param("startTime") String startTime, @Param("endTime")String endTime,@Param("equipmentId") long equipmentId);

    List<MesRecordExcept> faultAlarmRecord(@Param("equipmentId")long equipmentId, @Param("searchKey")String searchKey);

//    @Select("SELECT TRUNCATE(SUM(duration_hour)/(SELECT SUM(duration_hour) FROM mes_record_except WHERE equipment_id=#{equipmentId} and create_time BETWEEN #{startTime} AND #{endTime}),2) AS counts,DATE_FORMAT( create_time, '%Y-%m-%d' ) AS create_time FROM mes_record_except WHERE equipment_id=#{equipmentId} AND create_time BETWEEN #{startTime} AND #{endTime} GROUP BY DATE_FORMAT(create_time,'%Y-%m-%d') ORDER BY create_time ")
    @Select("SELECT IFNULL(FORMAT(SUM(duration_hour),2),0) AS counts,DATE_FORMAT( create_time, '%Y-%m-%d' ) AS currentDate FROM mes_record_except WHERE equipment_id=#{equipmentId} AND create_time BETWEEN #{startTime} AND #{endTime} GROUP BY DATE_FORMAT(create_time,'%Y-%m-%d') ORDER BY create_time ")
    List<MesRecordExcept> countMaintenanceTime(@Param("startTime") String startTime, @Param("endTime")String endTime,@Param("equipmentId") long equipmentId);

//    @Select(" SELECT TRUNCATE(COUNT(a.except_id)/(SELECT count(*) FROM mes_record_except WHERE equipment_id=#{equipmentId} and create_time BETWEEN #{startTime} AND #{endTime}),2) AS counts,a.except_id,b.except_message_cn FROM mes_record_except a LEFT JOIN mes_except b ON a.except_id = b.except_id WHERE 1=1 AND a.equipment_id=#{equipmentId}  AND a.create_time BETWEEN #{startTime} AND #{endTime} GROUP BY a.except_id ")
    @Select(" SELECT COUNT(a.except_id) AS counts,a.except_id,b.except_message_cn FROM mes_record_except a LEFT JOIN mes_except b ON a.except_id = b.except_id WHERE 1=1 AND a.equipment_id=#{equipmentId}  AND a.create_time BETWEEN #{startTime} AND #{endTime} GROUP BY a.except_id ORDER BY counts DESC LIMIT 0,20")
    List<MesRecordExcept> recordExceptCit(@Param("startTime") String startTime, @Param("endTime")String endTime,@Param("equipmentId") long equipmentId);

}
